#!/usr/bin/env python
import youtube_dl
import requests
import sys
import urllib.parse as urlparse
import sqlite3
import os
from prettytable import PrettyTable
from sqlite3 import Error
from urllib import request
from bs4 import BeautifulSoup
from pyaria2 import Aria2RPC
from urllib import parse

# Database location
database = "./database.db"


def item_exist(person_name):
    conn = create_connection(database)
    c = conn.cursor()
    try:
        c.execute("SELECT count(*) FROM person_items WHERE person_name = ?", (person_name,))
    except Error as e:
        print(e)
        sys.exit()

    return c.fetchone()[0]


def get_persion_pic_info(person_name):
    conn = create_connection(database)
    c = conn.cursor()
    try:
        c.execute("SELECT * FROM person_items WHERE person_name = ?", (person_name,))
    except Error as e:
        print(e)
        sys.exit()

    return c.fetchone()


def create_connection(db_file):
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except Error as e:
        print(e)
    return conn


def add_item(name_check):
    conn = create_connection(database)
    with conn:
        create_item(conn, name_check)


def delete_single_item(conn, id):
    sql = 'DELETE FROM person_items WHERE id=?'
    c = conn.cursor()
    c.execute(sql, (id,))
    conn.commit()


def delete_item(item_id):
    conn = create_connection(database)
    with conn:
        delete_single_item(conn, item_id)


def create_item(conn, item):
    sql = ''' INSERT INTO person_items(person_url,person_name,person_add,file_path)
              VALUES(?,?,?,?) '''
    c = conn.cursor()
    c.execute(sql, item)
    return c.lastrowid


# 检查数据库文件是否存在
def check_for_database():
    print("Running startup checks...")
    if os.path.exists(database):
        print("数据库存在")
    else:
        print("数据库不存在")
        print("Looks like this is your first time run...")
        first_run()


# 创建数据库
def create_table(conn, create_table_sql):
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
        print("Tables created.")
    except Error as e:
        print(e)


# 创建数据库
def create_tables():
    sql_create_person_table = """ CREATE TABLE IF NOT EXISTS person_items (
                                        id integer PRIMARY KEY,
                                        person_url text,
                                        person_name text,
                                        person_add text,
                                        file_path text
                                    ); 
                                    """

    # create a database connection
    conn = create_connection(database)

    # create tables
    if conn is not None:
        # create items table
        create_table(conn, sql_create_person_table)
    else:
        print("Error! cannot create the database connection.")


# #################################### Lets do it baby

# 第一次运行
def first_run():
    create_tables()
